# 线性编码版本3：引入配置文件
# 从配置文件获得各个服务器的信息
# 从配置文件获得本次测试要用的服务器的入口名/代号
import requests,pymysql, pandas, configparser

cf = configparser.ConfigParser()
cf.read('entry.ini', encoding='utf-8')
which_server = cf.get('entry', 'which_server')
which_db = cf.get('entry', 'which_db')
print(which_server, which_db)  # 调试代码
# 根据接口服务器入口名，获得接口服务器的信息
cf.read('server.conf', encoding='utf-8')
ip = cf.get(which_server, 'ip') 
port = cf.get(which_server, 'port')
host = f'http://{ip}:{port}'
print(host)
# 根据数据库服务器入口名，获得数据库服务器的信息
cf.read('db.conf', encoding='utf-8')
dbhost = cf.get(which_db, 'host')
db = cf.get(which_db, 'db')
user = cf.get(which_db, 'user')
passwd = cf.get(which_db, 'passwd')
dbinfo = {'host': dbhost, 'db': db, 'user': user, 'passwd': passwd}
print(dbinfo)

# #使用sql语句文件实现数据库初始化
#

# conn = pymysql.connect(host='192.168.196.128', db='wa_test', user='root', passwd='123456')
conn=pymysql.connect(**dbinfo)
cursor = conn.cursor()
sqls = open('apitest.sql', 'r', encoding='utf-8')
for sql in sqls:
    if sql.strip() and not sql.startswith('--'):
        cursor.execute(sql)
conn.commit()
conn.close()
# if sql.strip() and not sql.startswith('--'):
#     print(sql,end='')

# 执行excel中的测试用例
# import requests
# import pandas

data = pandas.read_excel('login.xlsx', usecols=['用例编号', '用例说明', '接口路径', '请求方法', '参数', '预期结果'])
case_info = data['用例编号'] + ':' + data['用例说明']
# url = 'http://192.168.196.128' + data['接口路径']
url=host+data['接口路径']
data.insert(0, '用例信息', case_info)
data.insert(1, '接口地址', url)
data.drop(['用例编号', '用例说明', '接口路径'], axis=1, inplace=True)
data['参数'] = data['参数'].apply(eval)
data['预期结果'] = data['预期结果'].apply(eval)
cases = data.values.tolist()
for case in cases:  # case是一条用例，相当于一行
    case_info, url, method, args, expect = case
    res = requests.post(url, args)
    actual = res.json()
    if actual == expect:
        print(f'{case_info}==响应结果对比通过')
    else:
        print(f'{case_info}==响应结果对比失败==预期：{expect}==实际：{actual}')

# import requests
# import pandas

data = pandas.read_excel('signup.xlsx', usecols=['用例编号', '用例说明', '接口路径', '请求方法', '参数', '预期结果', '验库sql', '预期行数'])
case_info = data['用例编号'] + ':' + data['用例说明']
url = host+ data['接口路径']
data.insert(0, '用例信息', case_info)
data.insert(1, '接口地址', url)
data.drop(['用例编号', '用例说明', '接口路径'], axis=1, inplace=True)
data['参数'] = data['参数'].apply(eval)
data['预期结果'] = data['预期结果'].apply(eval)
cases = data.values.tolist()
for case in cases:  # case是一条用例，相当于一行
    case_info, url, method, args, expect, check_sql, expect_rows = case
    # res=requests.post(url,json=args)
    res = eval(f"requests.{method}('{url}',json=args)")
    actual = res.json()
    if expect == actual:
        print(f'{case_info}==响应结果对比通过')
    else:
        print(f'{case_info}==响应结果对比失败==预期：{expect}==实际：{actual}')
    # 落库检查
    conn = pymysql.connect(**dbinfo)
    cursor = conn.cursor()
    cursor.execute(check_sql)
    actual_rows = cursor.fetchone()[0]
    if actual_rows == expect_rows:
        print(f'{case_info}==验库成功')
    else:
        print(f'{case_info}==验库失败==要检查的数据：{args}=预期行数：{expect_rows}==实际：{actual_rows}')
